﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

namespace TextMIS
{
    public partial class frmPutOut : Form
    {
        public frmPutOut()
        {
            InitializeComponent();
        }

        SqlConnection conn;
//        SqlCommand com;
        SqlDataAdapter da;
        System.Data.DataTable dt;
//        SqlDataReader dr;

        private void frmPutOut_Load(object sender, EventArgs e)
        {
            conn = new
SqlConnection(frmLogin.SQLstr);
            da = new SqlDataAdapter("select distinct RegisterID,Pname from viewPI_tab", conn);
            DataSet ds = new DataSet();
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            da.Fill(ds, "viewPI_tab");

            conn.Close();

            this.cbxChoose.DataSource = ds.Tables[0];
            this.cbxChoose.DisplayMember = "RegisterID";
            this.cbxChoose.ValueMember = "Pname";
        }

        private void btnPutOut_Click(object sender, EventArgs e)
        {
            ApplicationClass excel = new ApplicationClass();//打开Excel应用程序
            
            Workbook workbook = excel.Workbooks.Add(true);//新建工作簿对象

            string getResult = "";
            getResult = cbxChoose.Text;
            
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//引用工作表
            worksheet.Cells[1, 1] = "注册编号";//将值写入第1行第1列的单元格
            worksheet.Cells[1, 2] = "用户姓名";//将值写入第1行第2列的单元格
            worksheet.Cells[1, 3] = "联系方式";
            worksheet.Cells[1, 4] = "体检项目";
            worksheet.Cells[1, 5] = "体检科室";
            worksheet.Cells[1, 6] = "体检价格";
            worksheet.Cells[1, 7] = "体检结果";
            worksheet.Cells[1, 8] = "用户名";
            worksheet.Cells[1, 9] = "是否支付";
            
            da = new SqlDataAdapter("select RegisterID,Pname,Pphone,ItemName,BranchName,ItemPrice,Result,Username,PrePaid from viewPI_tab where RegisterID='" + cbxChoose.Text + "'", conn);//通过视图查询
            dt = new System.Data.DataTable();
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int count = da.Fill(dt);
            conn.Close();
            if (count == 0)
            {
                MessageBox.Show("该注册编号没有记录！");
                return;
            }
            int rowIndex = 1;  
	  
	            int colIndex = 0;  
	  
            //取得标题   
                //foreach (DataColumn col in dt.Columns)  
                //{  
                //    colIndex++;

                //    worksheet.Cells[1, colIndex] = col.ColumnName;  
                //}

            	            //取得表格中的数据   
                foreach (DataRow row in dt.Rows)  
	            {  
	                rowIndex++;  
	  
	                colIndex = 0;  
	  
	                foreach (DataColumn col in dt.Columns)  
	                {  
	                   colIndex++;

                       worksheet.Cells[rowIndex, colIndex] =   
                          
	                        row[col.ColumnName].ToString().Trim();  
   
	                }  
	            }
                excel.Visible = true;// Excel应用程序可见
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
